import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime, timedelta
from openpyxl import load_workbook, Workbook
import xls_style, os
from enum import Enum

# 初始化数据库
SQLALCHEMY_DATABASE_URL = "mysql+pymysql://root:yongs81@47.113.124.50:3306/demo"
engine = create_engine(SQLALCHEMY_DATABASE_URL)


def sheets(path):
    try:
        wb = load_workbook(path)
        s = wb.sheetnames
    except:
        s = []
    return s


def day_or_night(endtime):
    '''
    根据结束时间endtime，确定是白班还是夜班，并确定表名，列的list
    :param endtime:
    :return:
    '''
    if endtime.hour >= 20 or endtime.hour < 8:
        sheet_name = "{0}月{1}日-白班".format(endtime.month, endtime.day)
        date = "{0}-{1}-{2}".format(endtime.year, endtime.month, endtime.day)
        daynight = "白班"
        columns = {8: "8:00-9:00", 9: "9:00-10:00", 10: "10:00-11:00", 11: "11:00-12:00",
                   12: "12:00-13:00", 13: "13:00-14:00", 14: "14:00-15:00", 15: "15:00-16:00",
                   16: "16:00-17:00", 17: "17:00-18:00", 18: "18:00-19:00", 19: "19:00-20:00"}
        order = []
    elif endtime.hour >= 8:
        yestoday = endtime - timedelta(days=1)
        sheet_name = "{0}月{1}日-夜班".format(yestoday.month, yestoday.day)
        date = "{0}-{1}-{2}".format(endtime.year, yestoday.month, yestoday.day)
        daynight = "夜班"
        columns = {20: " 20:00-21:00", 21: " 21:00-22:00", 22: " 22:00-23:00", 23: " 23:00-00:00",
                   0: "00:00-01:00", 1: "01:00-02:00", 2: "02:00-03:00", 3: "03:00-04:00",
                   4: "04:00-05:00", 5: "05:00-06:00", 6: "06:00-07:00", 7: "07:00-08:00"}
        order = ["20:00-21:00", "21:00-22:00", "22:00-23:00", "23:00-00:00",
                 "00:00-1:00", "1:00-2:00", "2:00-3:00", "3:00-4:00",
                 "4:00-5:00", "5:00-6:00", "6:00-7:00", "7:00-8:00"]
    return sheet_name, columns, order, date, daynight


def is_workday(filepath, line, date, daynight):
    '''
    用于判断是否工作日
    :param filepath:存储整个月休息上班安排的excel文件路径
    :param line: 查询哪条线
    :param date: 查询哪一天，格式4-15
    :param daynight: 查询白班还是夜班，取值范围["白班"，"夜班"]
    :return: 上班则返回True，休息则返回False
    '''
    # filepath = r"C:\Users\hyq\Desktop\work.xlsx"
    df1 = pd.read_excel(filepath, engine="openpyxl", sheet_name="工作日")
    df1["日期"] = pd.to_datetime(df1["日期"])
    df1.set_index(["日期", "白夜班"], inplace=True)
    try:
        data = df1.at[(date, daynight), line]
    except:
        data = 0
    return data


def is_working(filepath, line, dtime, flag="start"):
    '''
    用于判断是否休息时间
    :param filepath: 存储各条线上班、休息的时间安排的excel文件路径
    :param line: 查询哪条线
    :param dtime: 查询时间，格式"08:00:00"
    :param flag: 是查询开始还是结束
    :return: 开工时间返回True，休息返回False
    '''
    df1 = pd.read_excel(filepath, engine="openpyxl", sheet_name="上班时间", dtype={"时间": str})
    df1.iloc[0, 0] = "00:00:00"
    df1.iloc[1, 0] = "00:00:00"
    df1.set_index(["时间", "开始结束"], inplace=True)
    try:
        data = df1.at[(dtime, flag), line]
    except:
        data = 0
    return data


class LineName(str, Enum):
    w1 = 'w1'
    w2 = 'w2'
    w4 = 'w4'
    w5 = 'w5'
    w6 = 'w6'
    w7 = 'w7'
    w9 = 'w9'
    w10 = 'w10'
    r290 = 'r290'


if __name__ == '__main__':
    files = []

    for line in LineName:
        # 定义时间起点
        now = datetime.now()
        # now = datetime(2020, 4, 30, 20, 0, 0)
        endtime = now
        starttime = endtime - timedelta(hours=12)

        while True:
            # 确定是白班还是夜班，并确定表名，列的list
            sheet_name, columns, order, date, daynight = day_or_night(endtime)

            # 默认储存文件的路径
            path = r".\file\{0}线{1}月份汇总数据.xlsx".format(line, now.month)
            path = os.path.abspath(path)
            workfile = r".\file\{0}月上班时间安排.xlsx".format(now.month)

            # todo 利用dataframe的特性，增加判断是否休息日
            # 如果是工作日
            if is_workday(workfile, line, date, daynight):
                # 转换成string格式，便于获得SQL语句
                start = starttime.strftime('%Y-%m-%d %H:%M:%S')
                end = endtime.strftime('%Y-%m-%d %H:%M:%S')
                # 在数据库内取得数据
                sql = "select * from analyse_pause where StartTime between '{0}' AND '{1}' AND isActive = True;".format(
                    start,
                    end)
                df = pd.read_sql_query(sql, engine)
                # 如果df不是空白
                if not df.empty:
                    # 筛选数据，线体是line，只选择"StartTime","StartLine","StartVarName","Interval"四列
                    df1 = df.loc[df["StartLine"] == line, ["StartTime", "StartLine", "StartVarName", "Interval"]]
                    # 将"StartTime"列设置为日期格式
                    df1.set_index(pd.to_datetime(df1["StartTime"]), inplace=True)
                    # 通过"StartVarName"和小时分组，并将"Interval"列进行求和
                    perhour = df1.groupby(["StartVarName", df1.index.hour])["Interval"].sum()
                    # 将时间index调整到columns
                    perhour = perhour.unstack()
                    # 遍历perhour，将所有空白数据调整为0
                    for i in perhour.columns:
                        perhour.fillna({i: 0}, inplace=True)
                        if i not in columns:
                            perhour.drop([i], axis=1, inplace=True)
                            print("%s列已删除" % i)
                    # 增加“合计”列
                    perhour['合计'] = perhour.sum(axis=1)
                    # 根据“合计”列倒序排列
                    perhour.sort_values(by="合计", ascending=False, inplace=True)
                    perhour.index.name = "急停点"
                    perhour.columns.name = "时间轴"
                    # 时间columns修改表达方式，并调整顺序
                    perhour.rename(columns=columns, inplace=True)
                    # if "夜班" in sheet_name:
                    #     perhour = perhour[order]
                    # 存储成xlsx文件
                    # 若果这个xlsx文件本身存在，则load，没有则新建一个xlsx文件
                    if os.path.exists(path):
                        book = load_workbook(path)
                    else:
                        book = Workbook()
                        book.save(path)
                        book = load_workbook(path)
                    # 以增加写入模式写入xlsx文件
                    with pd.ExcelWriter(path, engine='openpyxl') as writer:
                        writer.book = book
                        writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
                        perhour.to_excel(writer, sheet_name)
                        print("已完成%s的数据分析~~~" % sheet_name)
                        writer.save()
                endtime = starttime
                starttime = endtime - timedelta(hours=12)
            else:
                print("%s线%s，休息呢~~" % (line, sheet_name))
                endtime = starttime
                starttime = endtime - timedelta(hours=12)
            # 超过一个月的数据，作为while循环的跳出点
            if starttime.month < now.month:
                break

        # todo 格式化Excel文件，包括字体、线框、增加饼图、折线图
        if os.path.exists(path):
            files.append(path)
            wb = load_workbook(path)
            for sheet in wb.worksheets:
                if sheet.title == "Sheet":
                    wb.remove(wb["Sheet"])
                    wb.save(path)
                elif sheet["A2"].value:
                    print('正在处理%s' % sheet.title)
                    xls_style.format_table(sheet)
                    xls_style.fit_width(sheet, ['A'], [20])
                    xls_style.insert_pie(sheet)
                    xls_style.insert_line(sheet)
            wb.save(path)

    if files:
        import weixin

        mails = ['pengjiahua@hisense.com', 'zhuchenglin@hisense.com', 'wujunzz@hisense.com', 'dongxiqiang@hisense.com',
                 'lizhaogao@hisense.com', 'huangxin3@hisense.com', 'huangyilong@hisense.com',
                 'huangguanhui@hisense.com',
                 'zaixinjun@hisense.com']
        # mails = ['huangguanhui@hisense.com']
        title = "{0}月{1}日各条线急停汇总数据".format(now.month, now.day - 1)
        weixin.send_mail_by_hx(title, mails, files)
        for file in files:
            os.remove(file)
            print('已删除%s' % file)
